﻿using aier.dataaccess.model;
using Dapper;
using log4net;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace aier.dataaccess.dapper
{
    public class ScreenTeamDal
    {
        public ILog log { get; set; }
        public DapperHelper dapperHelper { get; set; }

        /// <summary>
        /// 获取所有筛查小组
        /// </summary>
        /// <returns></returns>
        public IEnumerable<ScreenTeamView> GetScreenAllTeams()
        {
            return dapperHelper.ExecueWithoutTransaction<IEnumerable<ScreenTeamView>>(a =>
            {
                string sql = @"select * from AIER_SCREENTEAM_INFO ";
                SqlBuilder sqlBuilder = new SqlBuilder();
                var sqlTemplate = sqlBuilder.AddTemplate(sql);
                return a.Query<ScreenTeamView>(sqlTemplate.RawSql);
            });
        }

        /// <summary>
        /// 根据负责人ID获取筛查小组
        /// </summary>
        /// <param name="ownerId">负责人ID</param>
        /// <returns></returns>
        public IEnumerable<ScreenTeamView> GetScreenTeams(Guid ownerId)
        {

            return dapperHelper.ExecueWithoutTransaction<IEnumerable<ScreenTeamView>>(a =>
            {
                string sql = @"select c.* from AIER_SCREENTEAM_INFO as c
                                 /**innerjoin**/  /**where**/ ";
                SqlBuilder sqlBuilder = new SqlBuilder();
                var sqlTemplate = sqlBuilder.AddTemplate(sql);
                sqlBuilder.InnerJoin(" SystemUserBase as u on u.domainname = c.OwnerName");
                sqlBuilder.Where("u.SystemUserId = @ownerId", new { ownerId = ownerId });
                return a.Query<ScreenTeamView>(sqlTemplate.RawSql, sqlTemplate.Parameters);
            });
        }

        /// <summary>
        /// 根据筛查小组名称查询当前筛查小组
        /// 原程序使用：selectTeamIdByOwerName
        /// </summary>
        /// <param name="ownername">筛查负责人域账号（AIERCHINA\Lancy）</param>
        /// <returns></returns>
        public ScreenTeamView QueryScreenTeamByOwnerName(string ownername)
        {
            return dapperHelper.ExecueWithoutTransaction<ScreenTeamView>(a =>
            {
                string sql = @"select * from AIER_SCREENTEAM_INFO  /**where**/ ";
                SqlBuilder sqlBuilder = new SqlBuilder();
                var sqlTemplate = sqlBuilder.AddTemplate(sql);
                sqlBuilder.Where("OwnerName = @OwnerName", new { OwnerName = ownername });
                return a.QueryFirstOrDefault<ScreenTeamView>(sqlTemplate.RawSql, sqlTemplate.Parameters);
            });
        }

        /// <summary>
        /// 根据筛查小组名称查询当前筛查小组
        /// 原程序使用：getScreenteam
        /// </summary>
        /// <param name="name">筛查小组名称</param>
        /// <param name="hospital">医院ID</param>
        /// <param name="ownerid">负责人ID</param>
        /// <param name="statecode">状态０：可用,２:停用</param>
        /// <returns></returns>
        public IEnumerable<ScreenTeamView> QueryScreenTeam(string name, string hospital, string ownerid, int statecode=0)
        {
            return dapperHelper.ExecueWithoutTransaction<IEnumerable<ScreenTeamView>>(a =>
            {
                string sql = @"select * from new_screenteam  /**where**/  ";
                SqlBuilder sqlBuilder = new SqlBuilder();
                DynamicParameters sqlParams = new DynamicParameters();
                var sqlTemplate = sqlBuilder.AddTemplate(sql);

                if (!string.IsNullOrEmpty(hospital))
                {
                    sqlBuilder.Where("  new_hospital=@new_hospital ");
                    sqlParams.Add("@new_hospital", hospital);
                }
                if (!string.IsNullOrEmpty(ownerid))
                {
                    sqlBuilder.Where("  OwnerId=@OwnerId ");
                    sqlParams.Add("@OwnerId", ownerid);
                }
                if (!string.IsNullOrEmpty(name))
                {
                    sqlBuilder.Where("  CHARINDEX(@new_name,new_name)>= 1 ");
                    sqlParams.Add("@new_name", name);
                }
                sqlBuilder.Where("  statecode=@statecode ");
                sqlParams.Add("@statecode", statecode);

                log.Info(string.Format("ScreenTeamDal 对像中,QueryScreenTeam ,SQL：'{0}' 参数：{1}", sqlTemplate.RawSql, sqlParams.ParametersToString(sqlTemplate.RawSql)));
                return a.Query<ScreenTeamView>(sqlTemplate.RawSql,sqlParams);
            });
        }

        /// <summary>
        /// 根据当前登录用户Id获取筛查小组
        /// </summary>
        /// <param name="ownerid">用户Id</param>
        /// <returns></returns>
        public ScreenTeamView QueryScreenTeamByOwnerId(Guid ownerid)
        {
            IEnumerable<ScreenTeamView> screenTeamViews = this.GetScreenTeams(ownerid);

            return screenTeamViews != null && screenTeamViews.Count() > 0 ? screenTeamViews.OrderBy(g => g.CreatedOn).FirstOrDefault() : null;
        }

        public List<dynamic> GetScreenTeamMemberByTeamid(string teamid)
        {
            return dapperHelper.ExecueWithoutTransaction<List<dynamic>>(a =>
            {
                string sql = @"select FullName from new_new_screenteam_systemuser as sc left join systemuser as s on s.systemuserid =sc.systemuserid  /**where**/  ";
            SqlBuilder sqlBuilder = new SqlBuilder();
            DynamicParameters sqlParams = new DynamicParameters();
            var sqlTemplate = sqlBuilder.AddTemplate(sql);

            if (!string.IsNullOrEmpty(teamid))
            {
                sqlBuilder.Where("new_screenteamid=@teamid ");
                sqlParams.Add("@teamid", teamid);
            }
                return a.Query<dynamic>(sqlTemplate.RawSql, sqlParams).ToList();
            });
        }

    }
}
